using System;
using System.Collections.Generic;
using System.Text;
using DM.Entity;
using System.Data;
using System.Data.SqlClient;

namespace DM.DAO
{
    public class TinTucDAO:BaseDao<TinTuc>
    {
        public override TinTuc Get(int id)
        {
            TinTuc tin = new TinTuc();
            SqlDataReader dataReader = SqlHelper.ExecuteReader(SqlHelper.DMConnnectString, "getTinTucByMa", id);
            try
            {
                if (dataReader.Read())
                {
                    tin.Ma = Common.ParseInt32(dataReader["Ma"].ToString());
                    tin.Tieude = dataReader["TieuDe"].ToString();
                    tin.Maloaitin = Common.ParseInt32(dataReader["MaLoaiTin"].ToString());
                    tin.Mota = dataReader["MoTa"].ToString();
                    tin.Ngaydang = Common.ParseDateTime(dataReader["NgayDang"].ToString());
                    tin.Ngonngu = Common.ParseInt32(dataReader["NgonNgu"].ToString());
                    tin.Noidung = dataReader["NoiDung"].ToString();
                    tin.Nsd = Common.ParseInt32(dataReader["NSD"].ToString());
                    tin.Tukhoa = dataReader["TuKhoa"].ToString();
                    tin.Uutien = dataReader["UuTien"] == null ? (bool)dataReader["UuTien"] : false;
                    tin.Luotxem = Common.ParseInt32(dataReader["LuotXem"].ToString());
                }
            }
            catch (Exception)
            {
                Common.Show("Error Connection!");
                throw;
            }
            finally {dataReader.Close();}
            return tin;
        }
        /// <summary>
        /// Lấy ra đối tượng tin tức theo tiêu đề của chúng
        /// </summary>
        /// <param name="name">String tiêu đề của tin tức</param>
        /// <returns>Trả về TinTuc entity</returns>
        public override TinTuc Get(string name)
        {
            TinTuc tinTuc = new TinTuc();
            SqlDataReader dataReader = SqlHelper.ExecuteReader(SqlHelper.DMConnnectString, "getTinTucByTieuDe", name);
            try
            {
                if (dataReader.Read())
                {
                    tinTuc.Ma = Common.ParseInt32(dataReader["Ma"].ToString());
                    tinTuc.Tieude = dataReader["TieuDe"].ToString();
                    tinTuc.Maloaitin = Common.ParseInt32(dataReader["MaLoaiTin"].ToString());
                    tinTuc.Mota = dataReader["MoTa"].ToString();
                    tinTuc.Ngaydang = Common.ParseDateTime(dataReader["NgayDang"].ToString());
                    tinTuc.Ngonngu = Common.ParseInt32(dataReader["NgonNgu"].ToString());
                    tinTuc.Noidung = dataReader["NoiDung"].ToString();
                    tinTuc.Nsd = Common.ParseInt32(dataReader["NSD"].ToString());
                    tinTuc.Tukhoa = dataReader["TuKhoa"].ToString();
                    tinTuc.Uutien = dataReader["UuTien"] == null ? (bool)dataReader["UuTien"] : false;
                    tinTuc.Luotxem = Common.ParseInt32(dataReader["LuotXem"].ToString());
                }
            }
            catch (Exception)
            {
                Common.Show("Error Connection!");
                throw;
            }
            finally {dataReader.Close();}
            return tinTuc;
        }
        /// <summary>
        /// Lấy ra tin tức bằng mã loại tin, số lượng tin và ngôn ngữ
        /// </summary>
        /// <param name="maLoaiTin"></param>
        /// <param name="soluong"></param>
        /// <param name="ngonNgu"></param>
        /// <returns></returns>
        public  List<TinTuc> Gets(int maLoaiTin,int soluong,int ngonNgu)
        {
            List<TinTuc> listTin = new List<TinTuc>();
            SqlDataReader dataReader = SqlHelper.ExecuteReader(SqlHelper.DMConnnectString,
                                                               "getTinTucByLoaiTin_SoLuong_NN", maLoaiTin, soluong,
                                                               ngonNgu);
            try
            {
                while (dataReader.Read())
                {
                    TinTuc tinTuc = new TinTuc();

                    tinTuc.Ma = Common.ParseInt32(dataReader["Ma"].ToString());
                    tinTuc.Tieude = dataReader["TieuDe"].ToString();
                    tinTuc.Maloaitin = Common.ParseInt32(dataReader["MaLoaiTin"].ToString());
                    tinTuc.Mota = dataReader["MoTa"].ToString();
                    tinTuc.Ngaydang = Common.ParseDateTime(dataReader["NgayDang"].ToString());
                    tinTuc.Ngonngu = Common.ParseInt32(dataReader["NgonNgu"].ToString());
                    tinTuc.Noidung = dataReader["NoiDung"].ToString();
                    tinTuc.Nsd = Common.ParseInt32(dataReader["NSD"].ToString());
                    tinTuc.Tukhoa = dataReader["TuKhoa"].ToString();
                    tinTuc.Uutien = dataReader["UuTien"] == null ? (bool)dataReader["UuTien"] : false;
                    tinTuc.Luotxem = Common.ParseInt32(dataReader["LuotXem"].ToString());

                    listTin.Add(tinTuc);
                }
            }
            catch (Exception)
            {
                Common.Show("Error Connection!");
                throw;
            }
            finally {dataReader.Close();}
            if (listTin.Count>0)
            return listTin;
            TinTuc tin = new TinTuc();
            listTin.Add(tin);
            return listTin;
        }

        public List<TinTuc> GetsByMaGoc(int maloaiCha, int soluong, int ngonNgu)
        {
            List<TinTuc> listTin = new List<TinTuc>();
            SqlDataReader dataReader = SqlHelper.ExecuteReader(SqlHelper.DMConnnectString,
                                                               "getTinTucByLoaiTinCha_SoLuong_NgonNgu", maloaiCha, soluong,
                                                               ngonNgu);
            try
            {
                while (dataReader.Read())
                {
                    TinTuc tinTuc = new TinTuc();

                    tinTuc.Ma = Common.ParseInt32(dataReader["Ma"].ToString());
                    tinTuc.Tieude = dataReader["TieuDe"].ToString();
                    tinTuc.Maloaitin = Common.ParseInt32(dataReader["MaLoaiTin"].ToString());
                    tinTuc.Mota = dataReader["MoTa"].ToString();
                    tinTuc.Ngaydang = Common.ParseDateTime(dataReader["NgayDang"].ToString());
                    tinTuc.Ngonngu = Common.ParseInt32(dataReader["NgonNgu"].ToString());
                    tinTuc.Noidung = dataReader["NoiDung"].ToString();
                    tinTuc.Nsd = Common.ParseInt32(dataReader["NSD"].ToString());
                    tinTuc.Tukhoa = dataReader["TuKhoa"].ToString();
                    tinTuc.Uutien = dataReader["UuTien"] == null ? (bool)dataReader["UuTien"] : false;
                    tinTuc.Luotxem = Common.ParseInt32(dataReader["LuotXem"].ToString());

                    listTin.Add(tinTuc);
                }
            }
            catch (Exception)
            {
                Common.Show("Error Connection!");
                throw;
            }
            finally {dataReader.Close();}
            return listTin;
        }

        /// <summary>
        /// Lay ra đối tượng tin tức theo mã tin và ngôn ngữ
        /// </summary>
        /// <param name="maloaitin"></param>
        /// <param name="ngonNgu"></param>
        /// <returns></returns>
        public TinTuc Get(int maloaitin, int ngonNgu)
        {
            TinTuc tinTuc= new TinTuc();
            SqlDataReader dataReader = SqlHelper.ExecuteReader(SqlHelper.DMConnnectString, "getTinTucByLoaiTinAndNgonNgu", maloaitin, ngonNgu);
            try
            {
                if (dataReader.Read())
                {
                    tinTuc.Ma = Common.ParseInt32(dataReader["Ma"].ToString());
                    tinTuc.Tieude = dataReader["TieuDe"].ToString();
                    tinTuc.Maloaitin = Common.ParseInt32(dataReader["MaLoaiTin"].ToString());
                    tinTuc.Mota = dataReader["MoTa"].ToString();
                    tinTuc.Ngaydang = Common.ParseDateTime(dataReader["NgayDang"].ToString());
                    tinTuc.Ngonngu = Common.ParseInt32(dataReader["NgonNgu"].ToString());
                    tinTuc.Noidung = dataReader["NoiDung"].ToString();
                    tinTuc.Nsd = Common.ParseInt32(dataReader["NSD"].ToString());
                    tinTuc.Tukhoa = dataReader["TuKhoa"].ToString();
                    tinTuc.Uutien = dataReader["UuTien"] == null ? (bool)dataReader["UuTien"] : false;
                    tinTuc.Luotxem = Common.ParseInt32(dataReader["LuotXem"].ToString());
                }
            }
            catch (Exception)
            {
                Common.Show("Error Connection!");
                throw;
            }
            finally {dataReader.Close();}
            return tinTuc;
        }

        /// <summary>
        /// Lấy ra danh sách tin theo thứ tự : Tin vip >> tin theo ngày.
        /// </summary>
        /// <param name="maLoaiTin"></param>
        /// <param name="soluong"></param>
        /// <param name="sortVip"></param>
        /// <param name="ngonNgu"></param>
        /// <returns></returns>
        public List<TinTuc> Gets(int maLoaiTin, int soluong, Boolean sortVip, int ngonNgu)
        {
            List<TinTuc> listTin = new List<TinTuc>();
            listTin = Gets(maLoaiTin, soluong,ngonNgu);
            List<TinTuc> returnList = new List<TinTuc>();
            if (sortVip)
            {
                foreach (TinTuc tinTuc in listTin)
                {
                    if (tinTuc.Uutien) returnList.Add(tinTuc);
                }
                foreach (TinTuc tinTuc in listTin)
                {
                    if (!tinTuc.Uutien) returnList.Add(tinTuc);
                }
                return returnList;
            }
            return listTin;
        }

        /// <summary>
        /// Lay ra list TinTuc theo ma tin goc, so luong, ngon ngu va co sap tin vip len dau khong (order by Ngay dang )
        /// </summary>
        /// <param name="maTinCha"></param>
        /// <param name="soluong"></param>
        /// <param name="sortVip"></param>
        /// <param name="ngonNgu"></param>
        /// <returns></returns>
        public List<TinTuc> GetsByMaGoc(int maTinCha, int soluong, Boolean sortVip, int ngonNgu)
        {
            List<TinTuc> listTin = new List<TinTuc>();
            listTin = GetsByMaGoc(maTinCha, soluong, ngonNgu);
            List<TinTuc> returnList = new List<TinTuc>();
            if (sortVip)
            {
                foreach (TinTuc tinTuc in listTin)
                {
                    if (tinTuc.Uutien) returnList.Add(tinTuc);
                }
                foreach (TinTuc tinTuc in listTin)
                {
                    if (!tinTuc.Uutien) returnList.Add(tinTuc);
                }
                return returnList;
            }
            return listTin;
        }


        public override int Insert(TinTuc t)
        {
            int Id = 0;
            object o = Id;
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.DMConnnectString, "InsertTinTuc", t.Maloaitin, t.Tieude, t.Mota, t.Noidung, t.Nsd, t.Ngaydang, t.Tukhoa, t.Luotxem, t.Uutien, t.Ngonngu,o);
            }
            catch (Exception Ex)
            {
                Common.Show(Ex.ToString());
                throw;
            }
            return (int) o;
        }

        public override int Update(TinTuc t)
        {
            SqlHelper.ExecuteNonQuery(SqlHelper.DMConnnectString, "UpdateTinTuc",t.Ma,t.Maloaitin,t.Tieude,t.Mota,t.Noidung,t.Nsd,t.Ngaydang,t.Tukhoa,t.Luotxem,t.Uutien,t.Ngonngu);
            return 0;
        }

        public override int Update(List<TinTuc> ts)
        {
            throw new NotImplementedException();
        }

        public override int Delete(string id)
        {
            SqlHelper.ExecuteNonQuery(SqlHelper.DMConnnectString, "DeleteTinTucByMa",id);
            return 0;
        }
        /// <summary>
        /// Lấy ra tất cả các tin tức
        /// </summary>
        /// <returns>Dataset</returns>
        public DataSet GetTinTucAll(int ngonngu)
        {
            DataSet dataSet = SqlHelper.ExecuteDataset(SqlHelper.DMConnnectString, "getTinTucAllNew", ngonngu);
            return dataSet;
        }

        public List<TinTuc> GetTinTucRandom(int ngonNgu)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.DMConnnectString);
            SqlCommand sqlCommand = new SqlCommand("select * from TinTuc where MaLoaiTin in (select Ma from LoaiTin where MaLoaiCha = 6) and NgonNgu="+ngonNgu, conn);
            List<TinTuc> listTin = new List<TinTuc>();
            try
            {
                conn.Open();
                SqlDataReader dataReader = sqlCommand.ExecuteReader();
                while (dataReader.Read())
                {
                    TinTuc tinTuc = new TinTuc();
                    tinTuc.Ma = Common.ParseInt32(dataReader["Ma"].ToString());
                    tinTuc.Tieude = dataReader["TieuDe"].ToString();
                    tinTuc.Maloaitin = Common.ParseInt32(dataReader["MaLoaiTin"].ToString());
                    tinTuc.Mota = dataReader["MoTa"].ToString();
                    tinTuc.Ngaydang = Common.ParseDateTime(dataReader["NgayDang"].ToString());
                    tinTuc.Ngonngu = Common.ParseInt32(dataReader["NgonNgu"].ToString());
                    tinTuc.Noidung = dataReader["NoiDung"].ToString();
                    tinTuc.Nsd = Common.ParseInt32(dataReader["NSD"].ToString());
                    tinTuc.Tukhoa = dataReader["TuKhoa"].ToString();
                    tinTuc.Uutien = dataReader["UuTien"] == null ? (bool)dataReader["UuTien"] : false;
                    tinTuc.Luotxem = Common.ParseInt32(dataReader["LuotXem"].ToString());
                    listTin.Add(tinTuc);
                }
                dataReader.Close();
            }
            catch (Exception ex)
            {
                Common.Show(ex.ToString());
            }
            finally
            {
                conn.Close();
            }
            
            List<TinTuc> returnList = new List<TinTuc>();
            Random so = new Random();
            int number1 = so.Next(1, listTin.Count);
            int number2;
            do
            {
                number2 = so.Next(1, listTin.Count);
            } while (number2==number1);
            returnList.Add(listTin[number1-1]);
            returnList.Add(listTin[number2-1]);
            return returnList;
        }

        /// <summary>
        /// Lay ra cac tin tuc theo MaTinGoc va NgonNgu
        /// </summary>
        /// <param name="maTinGoc">Ma cua loai Tin co MaTinCha la null</param>
        /// <param name="ngonNgu">1 tieng viet, 2 tieng anh</param>
        /// <returns></returns>
        public DataSet GetTinTucByLoaiTinGocVaNgonNgu(int maTinGoc, int ngonNgu)
        {
            DataSet data = SqlHelper.ExecuteDataset(SqlHelper.DMConnnectString, "getTinTucbyMaTinGocAndNgonNgu", maTinGoc,
                                                    ngonNgu);
            return data;
        }



        /// <summary>
        /// Lấy ra các tin thức theo mã loại tin và ngôn ngữ
        /// </summary>
        /// <param name="maLoaiTin"></param>
        /// <param name="ngonNgu"></param>
        /// <returns></returns>
        public DataSet GetTinTucByMaLoaiTinVaNgonNgu(int maLoaiTin, int ngonNgu)
        {
            DataSet dataSet = SqlHelper.ExecuteDataset(SqlHelper.DMConnnectString, "getTinTucByLoaiTinAndNgonNgu",
                                                       maLoaiTin, ngonNgu);
            return dataSet;
        }

        public DataTable Search(string keyWord)
        {
            DataSet dataSet = SqlHelper.ExecuteDataset(SqlHelper.DMConnnectString, "SearchTin", keyWord);
            return dataSet.Tables[0];
        }
    }
}
